﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.TMS.Driver;
using BCEntity.TMS.VehicleCategoryReference;
using BCEntity.TMS.VehicleType;
using Dapper;

namespace BCData.TMS.VehicleType
{
    public class VehicleTypeData : IVehicleTypeData
    {
        #region 私有变量

        //private readonly ISqlCommand sqlCommand;
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT A.VehicleTypeId,A.VehiclePicPath,A.VehicleTypeName,A.VehicleTypeEnum,A.AppliedTo,A.VehicleLength,
                                    A.VehicleWidth,A.VehicleHeight,A.VehicleLengthRatio,A.VehicleWidthRatio,A.VehicleHeightRatio,A.VehicleWheelbase,
                                    A.MaxWeight,A.MaxStere,A.Enabled,A.CreateTime,A.UpdateTime,A.Editer,A.OilConsumption,A.OilConsumptionPrice,A.EverdayProfit  
                                    FROM vehicletype A";

        #endregion 私有变量

        #region 构造函数

        public VehicleTypeData(//ISqlCommand sqlCommand,
            IDatabaseContext database)
        {
            //this.sqlCommand = sqlCommand;
            this.database = database;
        }

        #endregion 构造函数

        #region 公共方法

        public async Task<VehicleTypeEntity> InserAsync(VehicleTypeEntity entity, List<VehicleCategoryReferenceEntity> entities = null)
        {
            var vehicleType = await database.Master.InsertAsync(entity);
            if (vehicleType != null && entities.Any())
            {
                foreach (var item in entities)
                {
                    item.VehicleTypeId = vehicleType.VehicleTypeId;
                }
                var result = await database.Master.InsertAllAsync(entities);
                if (result > 0)
                {
                    return vehicleType;
                }
            }
            return null;
        }

        public async Task<VehicleTypeEntity> UpdateAsync(VehicleTypeEntity entity, List<VehicleCategoryReferenceEntity> entities = null)
        {
            var vehicleType = await database.Master.UpdateAsync(entity);
            if (vehicleType != null && entities.Any())
            {
                string sql = "Delete from  vehiclecategoryreference Where VehicleTypeId=?VehicleTypeId;";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?VehicleTypeId", entity.VehicleTypeId, DbType.Int32);
                var executeResult = await database.Master.ExecuteAsync(sql, parameters);
                if (executeResult > 0)
                {
                    //车辆分类先删除再新增
                    var result = await database.Master.InsertAllAsync(entities);
                    if (result > 0)
                    {
                        return vehicleType;
                    }
                }
            }
            return null;
        }

        public async Task<int> UpdateAsync(int vehicleTypeId, bool isEnabled)
        {
            string sql = SqlCommands.GetUpdateSql("vehicletype", new string[] { "Enabled" }, new string[] { "VehicleTypeId" });

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Enabled", isEnabled, DbType.Boolean);
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);

            //return await sqlCommand.ExecuteAsync(sql, CommandType.Text, parameters);
            return await database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }

        public async Task<int> DeleteAsync(int vehicleTypeId)
        {
            string sql = @"DELETE FROM vehicletype WHERE 1=1 AND VehicleTypeId=?VehicleTypeId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId);
            //return await sqlCommand.ExecuteAsync(sql, CommandType.Text, parameters);
            return await database.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<VehicleTypeExpandEntity> GetByIdAsync(int id)
        {
            #region SQL
            string sql = @" SELECT
                        A.VehicleTypeId,
	                    A.VehiclePicPath,
	                    A.VehicleTypeName,
	                    A.VehicleTypeEnum,
	                    A.AppliedTo,
	                    A.VehicleLength,
	                    A.VehicleWidth,
	                    A.VehicleHeight,
	                    A.VehicleLengthRatio,
	                    A.VehicleWidthRatio,
	                    A.VehicleHeightRatio,
	                    A.VehicleWheelbase,
	                    A.MaxWeight,
	                    A.MaxStere,
	                    A.Enabled,
	                    A.CreateTime,
	                    A.UpdateTime,
	                    A.Editer,
						A.OilConsumption,
						A.OilConsumptionPrice,
						A.EverdayProfit
                        FROM
	                        vehicletype A
	                        INNER JOIN   dictionaryreference B ON A.VehicleTypeEnum = B.SearchKey
                            WHERE   A.VehicleTypeId=?VehicleTypeId 
                            LIMIT 1;";

            #endregion SQL

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", id);
            //return await sqlCommand.QueryEntityAsync<VehicleTypeExpandEntity>(sql, CommandType.Text, parameters);
            return await database.Slave.QueryEntityAsync<VehicleTypeExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<VehicleTypeEntity>> GetAsync(string vehicleTypeName, string vehicleTypeEnum)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            if (!string.IsNullOrWhiteSpace(vehicleTypeName))
            {
                wheres.Add("A.VehicleTypeName=?VehicleTypeName");
                parameters.Add("?VehicleTypeName", vehicleTypeName);
            }

            if (!string.IsNullOrWhiteSpace(vehicleTypeEnum))
            {
                wheres.Add("A.VehicleTypeEnum=?VehicleTypeEnum");
                parameters.Add("?VehicleTypeEnum", vehicleTypeEnum);
            }

            wheres.Add("A.Enabled=?Enabled");
            parameters.Add("?Enabled", true);
            string sql = string.Format("{0} {1} ", QUERY,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);
            //return await sqlCommand.QueryAsync<VehicleTypeEntity>(sql, CommandType.Text, parameters);
            return await database.Slave.QueryAsync<VehicleTypeEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<VehicleTypeExpandEntity>, int)> GetAsync(string categoryReferenceIdStr, int? vehicleTypeId, string vehicleTypeEnum,
            int? maxStartWeight, int? maxEndWeight, decimal? maxStartStere, decimal? maxEndStere, bool? enabled, int pageIndex, int pageSize)
        {
            #region SQL

            string sql = @"SELECT
	                    A.VehicleTypeId,
	                    A.VehiclePicPath,
	                    A.VehicleTypeName,
	                    A.VehicleTypeEnum,
	                    A.AppliedTo,
	                    A.VehicleLength,
	                    A.VehicleWidth,
	                    A.VehicleHeight,
	                    A.VehicleLengthRatio,
	                    A.VehicleWidthRatio,
	                    A.VehicleHeightRatio,
	                    A.VehicleWheelbase,
	                    A.MaxWeight,
	                    A.MaxStere,
	                    A.Enabled,
	                    A.CreateTime,
	                    A.UpdateTime,
	                    A.Editer,
						A.OilConsumption,
						A.OilConsumptionPrice,
						A.EverdayProfit,
	                    B.ItemValue AS VehicleTypeValue,
	                    C.VehicleCategoryReferenceIdStr
                    FROM
	                    VehicleType A
	                    LEFT JOIN DictionaryReference B ON A.VehicleTypeEnum = B.SearchKey
	                    LEFT JOIN (
                                SELECT GROUP_CONCAT( VehicleCategoryReferenceId ) VehicleCategoryReferenceIdStr,VehicleTypeId
                                    FROM VehicleCategoryReference
                                    GROUP BY VehicleTypeId
                        ) C
	                    ON A.VehicleTypeId = C.VehicleTypeId";
            var countSql = @" SELECT COUNT(1) FROM
	                           VehicleType A
	                           LEFT JOIN DictionaryReference B ON A.VehicleTypeEnum = B.SearchKey
	                           LEFT JOIN (SELECT GROUP_CONCAT(VehicleCategoryReferenceId) VehicleCategoryReferenceIdStr,VehicleTypeId
                                            FROM VehicleCategoryReference
                                            GROUP BY VehicleTypeId) C
	                            ON A.VehicleTypeId = C.VehicleTypeId";

            #endregion SQL

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId ");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(vehicleTypeEnum))
            {
                wheres.Add(" A.VehicleTypeEnum=?vehicleTypeEnum ");
                parameters.Add("?vehicleTypeEnum", vehicleTypeEnum, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(categoryReferenceIdStr))
            {
                wheres.Add(" FIND_IN_SET(C.VehicleCategoryReferenceIdStr,?VehicleCategoryReferenceIdStr)");
                parameters.Add("?VehicleCategoryReferenceIdStr", categoryReferenceIdStr.Trim(','), DbType.AnsiString);
            }
            if (maxStartWeight.HasValue)
            {
                wheres.Add("A.MaxWeight >= ?MaxStartWeight");
                parameters.Add("?MaxStartWeight", maxStartWeight.Value, DbType.Int32);
            }
            if (maxEndWeight.HasValue)
            {
                wheres.Add("A.MaxWeight <= ?MaxEndWeight");
                parameters.Add("?MaxEndWeight", maxEndWeight.Value, DbType.Int32);
            }
            if (maxStartStere.HasValue)
            {
                wheres.Add("A.MaxStere >= ?MaxStartStere");
                parameters.Add("?MaxStartStere", maxStartStere.Value, DbType.Decimal);
            }
            if (maxEndStere.HasValue)
            {
                wheres.Add("A.MaxStere <= ?MaxEndStere");
                parameters.Add("?MaxEndStere", maxEndStere.Value, DbType.Decimal);
            }
            if (enabled.HasValue)
            {
                wheres.Add(" A.Enabled = ?Enabled ");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            sql = string.Format("{0} {1} ORDER BY  A.VehicleTypeId LIMIT {3},{4} ; {2} {1}",
                sql,
                wheres.Count > 0 ? " WHERE " + string.Join("AND", wheres) : "",
                countSql,
                (pageIndex - 1) * pageSize,
                pageSize);
            //return await sqlCommand.QueryMultipleAsync<VehicleTypeExpandEntity, int>(sql, CommandType.Text, parameters);
            return await database.Slave.QueryPageableListAsync<VehicleTypeExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<VehicleTypeExpandEntity>> GetVehicleTypes()
        {
            string sql = string.Format(@"SELECT A.VehicleTypeId,A.VehiclePicPath,A.VehicleTypeName,A.VehicleTypeEnum,A.AppliedTo,A.VehicleLength,
                            A.VehicleWidth,A.VehicleHeight,A.VehicleLengthRatio,A.VehicleWidthRatio,A.VehicleHeightRatio,A.VehicleWheelbase,
                            A.MaxWeight,A.MaxStere,A.Enabled,A.CreateTime,A.UpdateTime,A.Editer,A.OilConsumption,A.OilConsumptionPrice,A.EverdayProfit,B.ItemValue As VehicleTypeValue 
                            FROM VehicleType A
                            INNER JOIN DictionaryReference B ON A.VehicleTypeEnum=B.SearchKey ", QUERY);
            //var result = sqlCommand.QueryAsync<VehicleTypeExpandEntity>(sql, CommandType.Text);
            //return result;
            return await database.Slave.QueryAsync<VehicleTypeExpandEntity>(sql, null);
        }

        public async Task<string> GetVehicleTypeImg(string searchKey)
        {
            var sql = @" SELECT Address
                         FROM
	                        dictionaryreference A
	                        INNER JOIN materialconfiguration B ON A.SearchKey = B.UsageGroupCode
                        WHERE A.SearchKey =?SearchKey  ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SearchKey", searchKey);
            //var result = await sqlCommand.ExecuteScalarAsync(sql, CommandType.Text, parameters);

            //return result?.ToString();
            return await database.Slave.QueryEntityAsync<string>(sql, parameters);
        }

        public async Task<object> CheckVehicleTypeExists(int vehicleTypeId)
        {
            string sql = @"SELECT 1 FROM VehicleType WHERE VehicleTypeId=?VehicleTypeId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            //return sqlCommand.ExecuteScalarAsync(sql, CommandType.Text, parameters);
            return await database.Master.ExecuteScalarAsync(sql, parameters);
        }

        public async Task<bool> CheckVehicleTypeExistsAsync(int vehicleTypeId)
        {
            string sql = @"SELECT 1 FROM VehicleType WHERE VehicleTypeId=?VehicleTypeId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            //var res = await sqlCommand.ExecuteScalarAsync(sql, CommandType.Text, parameters);
            var res = await database.Slave.ExecuteScalarAsync(sql, parameters);
            return res.ToInt32() > 0;
        }

        public async Task<MaxVehicleTypeValueEntity> MaxVehicleTypeValue()
        {

            string sql = @"SELECT MAX(MaxStere) MaxStere, MAX(MaxWeight) MaxWeight FROM VehicleType WHERE Enabled =1;";

            //return await sqlCommand.QueryEntityAsync<MaxVehicleTypeValueEntity>(sql, CommandType.Text, null);
            return await database.Slave.QueryEntityAsync<MaxVehicleTypeValueEntity>(sql, null);
        }

        public async Task<VehicleTypeEntity> GetVehicle(int vehicleEndWeight, decimal vehicleEndStere)
        {
            DynamicParameters parameters = new DynamicParameters();

            string sql = string.Format("{0} WHERE VehicleEndWeight>=?Value Order BY VehicleEndWeight ASC LIMIT 1", QUERY);

            if (vehicleEndWeight > 0)
            {
                parameters.Add("?Value", vehicleEndWeight, DbType.Int32, size: 11);
            }
            else if (vehicleEndStere > 0)
            {
                sql = string.Format("{0} WHERE VehicleEndStere>=?Value  Order BY VehicleEndStere ASC LIMIT 1", QUERY);
                parameters.Add("?Value", vehicleEndStere, DbType.Decimal);
            }
            else
            {
                return default;
            }
            //return await sqlCommand.QueryEntityAsync<VehicleTypeEntity>(sql, CommandType.Text, parameters);
            return await database.Slave.QueryEntityAsync<VehicleTypeEntity>(sql, parameters);
        }

        public async Task<VehicleTypeEntity> GetByVehicleTypeName(string vehicleTypeName)
        {
            return await database.Slave.QueryEntityAsync<VehicleTypeEntity>($"{QUERY} WHERE A.VehicleTypeName = ?VehicleTypeName LIMIT 1", new { vehicleTypeName });
        }

        public async Task<IEnumerable<VehicleTypeEntity>> GetList()
        {
            return await database.Slave.QueryAsync<VehicleTypeEntity>(QUERY);
        }
        #endregion 公共方法

        public async Task<IEnumerable<VehicleTypeExpandEntity>> GetListByAgentRecordId(long? agentRecordId)
        {
            string sql = @"SELECT
	                    A.VehicleTypeId,
	                    A.VehiclePicPath,
	                    A.VehicleTypeName,
	                    A.VehicleTypeEnum,
	                    A.AppliedTo,
	                    A.VehicleLength,
	                    A.VehicleWidth,
	                    A.VehicleHeight,
	                    A.VehicleLengthRatio,
	                    A.VehicleWidthRatio,
	                    A.VehicleHeightRatio,
	                    A.VehicleWheelbase,
	                    A.MaxWeight,
	                    A.MaxStere,
	                    CASE WHEN A.Enabled = 0 THEN A.Enabled ELSE (CASE WHEN D.Enabled IS NULL THEN A.Enabled ELSE D.Enabled END) END AS Enabled,
	                    A.CreateTime,
	                    A.UpdateTime,
	                    A.Editer,
						A.OilConsumption,
						A.OilConsumptionPrice,
						A.EverdayProfit,
	                    B.ItemValue AS VehicleTypeValue,
	                    C.VehicleCategoryReferenceIdStr
                    FROM
	                    VehicleType A
	                    LEFT JOIN DictionaryReference B ON A.VehicleTypeEnum = B.SearchKey
	                    LEFT JOIN (
                                SELECT GROUP_CONCAT( VehicleCategoryReferenceId ) VehicleCategoryReferenceIdStr,VehicleTypeId
                                    FROM VehicleCategoryReference
                                    GROUP BY VehicleTypeId
                        ) C
	                    ON A.VehicleTypeId = C.VehicleTypeId
                        LEFT JOIN AgentVehicleType D ON D.VehicleTypeId = A.VehicleTypeId ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                sql = $"{sql} AND D.AgentRecordId = ?AgentRecordId";
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            wheres.Add("A.AppliedTo = ?AppliedTo");
            parameters.Add("?AppliedTo", DriverRoleType.Driver.ToString(), DbType.AnsiString);
            wheres.Add("CASE WHEN D.Enabled IS NULL THEN A.Enabled = ?Enabled ELSE D.Enabled = ?Enabled END");
            parameters.Add("?Enabled", true, DbType.Boolean);
            sql = string.Format("{0} {1} ORDER BY VehicleLength", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await database.Slave.QueryAsync<VehicleTypeExpandEntity>(sql, parameters);
        }
    }
}